/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("Student",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.MyTable.IServices;
using Microsoft.AspNetCore.Authorization;
using VOL.Core.Utilities;
using System.Linq;
using VOL.MyTable.Repositories;
using VOL.Core.DBManager;
using System.Collections;
using VOL.System.Services.System;

namespace VOL.MyTable.Controllers
{
    public partial class StudentController
    {
        private readonly IStudentService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public StudentController(
            IStudentService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }

        /// <summary>
        /// repository.仅限在表xxx.Service.cs中使用,DBServerProvider可以访问数据库
        /// </summary>
       [HttpPost, Route("testDB"), AllowAnonymous]
        public Hashtable testDB()
        {
            Hashtable rtn_hst = new Hashtable();
            
            IQueryable<Stud_grade> queryeable= DBServerProvider.DbContext.Set<Stud_grade>();


            //groupby中的1表示全部数据
            var resuale_obj= queryeable.GroupBy(x => 1).Select(x => new
            {
                grade = x.Sum(o => o.grade)
            }).FirstOrDefault();

            rtn_hst.Add("sg_table", resuale_obj);
            rtn_hst.Add("rows", queryeable.Count());
            return rtn_hst;
        }
        [HttpPost, Route("test_AD"), AllowAnonymous]
        public string test_AD(string username,string password)
        {
            ValidateAD ad = new ValidateAD();
            return ad.ADLogin(username, password);
        }

        [HttpPost, Route("test_sql"), AllowAnonymous]
        public dynamic test_sql()
        {
            //SqlDapper中的in操作如果用参数
            string tsql = "SELECT *, A+B+C+D+E AS 'total' FROM ( SELECT  SUM(case when q1 = 'A' then 1 else 0 end)+ sum(case when q2 = 'A' then 1 else 0 end )+ sum(case when q3 = 'A' then 1 else 0 end )+ sum(case when q4 = 'A' then 1 else 0 end )+ sum(case when q5 = 'A' then 1 else 0 end )+ sum(case when q6 = 'A' then 1 else 0 end )+ sum(case when q7 = 'A' then 1 else 0 end )+ sum(case when q8 = 'A' then 1 else 0 end )+ sum(case when q9 = 'A' then 1 else 0 end )+ sum(case when q10 = 'A' then 1 else 0 end )  AS 'A',  SUM(case when q1 = 'B' then 1 else 0 end)+ sum(case when q2 = 'B' then 1 else 0 end )+ sum(case when q3 = 'B' then 1 else 0 end )+ sum(case when q4 = 'B' then 1 else 0 end )+ sum(case when q5 = 'B' then 1 else 0 end )+ sum(case when q6 = 'B' then 1 else 0 end )+ sum(case when q7 = 'B' then 1 else 0 end )+ sum(case when q8 = 'B' then 1 else 0 end )+ sum(case when q9 = 'B' then 1 else 0 end )+ sum(case when q10 = 'B' then 1 else 0 end )  AS 'B',  SUM(case when q1 = 'C' then 1 else 0 end)+ sum(case when q2 = 'C' then 1 else 0 end )+ sum(case when q3 = 'C' then 1 else 0 end )+ sum(case when q4 = 'C' then 1 else 0 end )+ sum(case when q5 = 'C' then 1 else 0 end )+ sum(case when q6 = 'C' then 1 else 0 end )+ sum(case when q7 = 'C' then 1 else 0 end )+ sum(case when q8 = 'C' then 1 else 0 end )+ sum(case when q9 = 'C' then 1 else 0 end )+ sum(case when q10 = 'C' then 1 else 0 end )  AS 'C',  SUM(case when q1 = 'D' then 1 else 0 end)+ sum(case when q2 = 'D' then 1 else 0 end )+ sum(case when q3 = 'D' then 1 else 0 end )+ sum(case when q4 = 'D' then 1 else 0 end )+ sum(case when q5 = 'D' then 1 else 0 end )+ sum(case when q6 = 'D' then 1 else 0 end )+ sum(case when q7 = 'D' then 1 else 0 end )+ sum(case when q8 = 'D' then 1 else 0 end )+ sum(case when q9 = 'D' then 1 else 0 end )+ sum(case when q10 = 'D' then 1 else 0 end )  AS 'D',  SUM(case when q1 = 'E' then 1 else 0 end)+ sum(case when q2 = 'E' then 1 else 0 end )+ sum(case when q3 = 'E' then 1 else 0 end )+ sum(case when q4 = 'E' then 1 else 0 end )+ sum(case when q5 = 'E' then 1 else 0 end )+ sum(case when q6 = 'E' then 1 else 0 end )+ sum(case when q7 = 'E' then 1 else 0 end )+ sum(case when q8 = 'E' then 1 else 0 end )+ sum(case when q9 = 'E' then 1 else 0 end )+ sum(case when q10 = 'E' then 1 else 0 end )  AS 'E',  SUM(case when q1 = 'F' then 1 else 0 end)+ sum(case when q2 = 'F' then 1 else 0 end )+ sum(case when q3 = 'F' then 1 else 0 end )+ sum(case when q4 = 'F' then 1 else 0 end )+ sum(case when q5 = 'F' then 1 else 0 end )+ sum(case when q6 = 'F' then 1 else 0 end )+ sum(case when q7 = 'F' then 1 else 0 end )+ sum(case when q8 = 'F' then 1 else 0 end )+ sum(case when q9 = 'F' then 1 else 0 end )+ sum(case when q10 = 'F' then 1 else 0 end )  AS 'F'  FROM it_survey ) as a";
            List<dynamic> tt = DBServerProvider.SqlDapper.QueryList<dynamic>(tsql,null).ToList();
            return tt;
        }
    }
}
